/****************************** Module Header ******************************\
 * Module Name:  FileDataFormater.cs
 * Module Version:  1.0.0 (2011-9-28)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to process the different format data file .It supplies
 * public method to convert a data format to others data format.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.IO;

namespace UCF.Core.Format
{
    /// <summary>
    /// 文件数据格式化
    /// <para>提供一种数据格式的文件转换为另一种数据格式的文件的方法</para>
    /// </summary>
    public class FileDataFormater
    {
        /// <summary>
        /// 将Excel格式的中国省市(国标)数据转换为XML文件格式
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="excelSheetName"></param>
        /// <param name="xmlFilePath"></param>
        /// <param name="zipXMLFilePath"></param>
        /// <returns></returns>
        /// <example>ExcelToXMLForChinaRegion(Server.MapPath("cnregion.xls"), "2011", "E:\\a.xml", "E:\\b.xml");</example>
        public bool ExcelToXMLForChinaRegion(string excelFilePath, string excelSheetName, string xmlFilePath, string zipXMLFilePath)
        {
            string strXML = "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" + Environment.NewLine;
            strXML += "<cnregion>";
            string strProvince = "";
            string strCity = "";
            string strArea = "";
            string strCode = "";
            IDataReader dr = LoadFromExcel(@excelFilePath, excelSheetName);
            bool blnProvinceFirst = true;
            bool blnCityFirst = true;
            while (dr.Read())
            {
                strCode = dr[0].ToString();
                strProvince = dr[1].ToString();
                strCity = dr[2].ToString();
                strArea = dr[3].ToString();

                if (strProvince.Length > 0)
                {
                    if (blnProvinceFirst)
                    {
                        strProvince = Environment.NewLine + "\t<province code=\"" + strCode + "\" name=\"" + strProvince + "\">";
                        blnProvinceFirst = false;
                    }
                    else
                    {
                        strProvince = Environment.NewLine + "\t\t</city>" + Environment.NewLine + "\t</province>" + Environment.NewLine + "\t<province code=\"" + strCode + "\" name=\"" + strProvince + "\">";
                        blnCityFirst = true;
                    }
                    strXML += strProvince;
                }
                if (strCity.Length > 0)
                {
                    if (blnCityFirst)
                    {
                        strCity = Environment.NewLine + "\t\t<city code=\"" + strCode + "\" name=\"" + strCity + "\">";
                        blnCityFirst = false;
                    }
                    else
                    {
                        strCity = Environment.NewLine + "\t\t</city>" + Environment.NewLine + "\t\t<city code=\"" + strCode + "\" name=\"" + strCity + "\">";
                    }

                    strXML += strCity;
                }
                if (strArea.Length > 0) strArea = Environment.NewLine + "\t\t\t<area code=\"" + strCode + "\" name=\"" + strArea + "\" />";
                strXML += strArea;
            }
            strXML += Environment.NewLine + "\t\t</city>" + Environment.NewLine + "\t</province>" + Environment.NewLine + "</cnregion>";
            if (@zipXMLFilePath.Length > 0) File.WriteAllText(@zipXMLFilePath, strXML.Replace("\t", "").Replace(Environment.NewLine, ""));
            File.WriteAllText(@xmlFilePath, strXML);
            return true;
        }
        /// <summary>
        /// 将Excel格式的中国省市(国标)数据转换为XML文件格式
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="excelSheetName"></param>
        /// <param name="xmlFilePath"></param>
        /// <returns></returns>
        public bool ExcelToXMLForChinaRegion(string excelFilePath, string excelSheetName, string xmlFilePath)
        {
            return this.ExcelToXMLForChinaRegion(excelFilePath, excelSheetName, xmlFilePath, "");
        }

        /// <summary>
        /// 打开Excel,生成包含Excel的IDataReader对象，Excel只支持97-03格式
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="firstRowIsTitle">第一行是否是标题(起始数据在第二行或者第一行)</param>
        /// <returns></returns>
        public IDataReader LoadFromExcel(string filePath, string sheetName="Sheet1", bool firstRowIsTitle=true)
        {
            return GetDataSetFromExcel(filePath, sheetName, firstRowIsTitle).CreateDataReader();
        }

        public DataSet GetDataSetFromExcel(string filePath, string sheetName = "Sheet1", bool firstRowIsTitle = true)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=" + ((firstRowIsTitle) ? "Yes" : "No") + ";IMEX=1'";
            OleDbConnection OleConn = new OleDbConnection(strConn);
            OleConn.Open();
            String sql = "SELECT * FROM  [" + sheetName + "$]";//可是更改Sheet名称，比如sheet2，等等    

            OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
            DataSet OleDsExcle = new DataSet();
            OleDaExcel.Fill(OleDsExcle, sheetName);
            OleConn.Close();
            return OleDsExcle;
        }
    }
}
